<?php
yii数据库操作


一：查询
    1：多条件查询：
    $where[] = 'and'; // 多条件时必须，如果where无查询，会自动过滤
    $wehre[] = ['=', 'phone_id', $phone_id];
    $where[] = ['in', 'id', $ids];
    $where[] = ['or' , 
                    ['=' , 'id' , 1] ，
                    ['=' , 'id' , 3]
                ];
    self::find()->where($where)->all();

    2：排序
     => ['first_name' => SORT_ASC, 'last_name' => SORT_DESC],

    3： 命令行
    $ccv2db = \Yii::$app->ccv2db;
    $data = $ccv2db->createCommand("
    select * from (
        SELECT id,group_id,u_time,c_time FROM `weixin_group_log` WHERE `group_id` IN (" . implode(',', $relate_group_id) . ") ORDER BY `id` DESC 
        ) as t 
    GROUP BY group_id
    ")->queryAll();

    4：orderBy
    正确：->orderBy(['id' => SORT_DESC])
    错误：->orderBy('id',SORT_DESC)
    
二：插入
    1：单一插入
        $model = new self();
        $model->attributes = $data;
        $model->save();

    2：数组插入
        $model = new self();
        $model->setAttributes([
            'student_id' => $data['student_id'],
            'textbook_id' => $data['textbook_id'],
        ], true); // true 验证rules false 不验证
        $model->save();
        
    3：批量插入
        self::getDb()->createCommand()->batchInsert(self::tableName(), $keys, $data)->execute();
        // 备注：$key 是插入的字段名 $data 是一个二位数组

    4：mysql 事物
        try {
            /*
                事物级别
                \yii\db\Transaction::READ_UNCOMMITTED
                \yii\db\Transaction::READ_COMMITTED
                \yii\db\Transaction::REPEATABLE_READ
                \yii\db\Transaction::SERIALIZABLE
            */
            $trans = Yii::$app->db->beginTransaction(\yii\db\Transaction::SERIALIZABLE);
            $trans->commit();
        } catch (Exception $e) {
            $trans->rollback();
            return false;
        }

        ClassdirectorClassInfoModel::getDb()->enableSlaves = false;
        $limi_xueguan_class = ClassdirectorClassStudentModel::getDb()->beginTransaction();
        $limi_xueguan_class->commit();
        $limi_xueguan_import->rollBack();

    5: 禁用从库
    Order::getDb()->enableSlaves=false;
    
三：更新
    self::updateAll([
        '待更新数据' => []
    ], [
        '条件' => []
    ]);


四：删除
    self::deleteAll(['id' => $id]);

五：错误
    print_r($model->getErrors());



六：打印sql语句
     $model->find()->createCommand()->getRawSql();

        // 查询班级与人数
        $classStudent = CabintestStudentPool::find()->select([
            'class_name' => 'class_name',
            'student_num' => 'count(class_name)',
        ])->where([
            'type' => 5,
            'school_id' => $input['school_id'],
        ])->groupBy(['class_name'])->asArray()->all();
        
Yii 1.0数据库操作 查询、增加、更新、删除
https://blog.csdn.net/mafey/article/details/52103358




/*
table 获取页面数据排重
var table = $(".table-condensed>tbody>tr").find("td:eq(0)");
var length = table.length;
var array = ''
for(var i=0; i<length; i++) {
  array += table.eq(i).text() + ',';
}
console.log(array);


select *,count(*) as count from (
    SELECT id,manage_id,status,relate_group_id,qrcode_name FROM `wechat_group` WHERE manage_id in (
    902,901,900,899,898,897,896,895,894,893,892,891,890,889,888,887,886,885,884,883,882,881,880,879,878,877,876,875,874,873,872,871,870,869,868,867,866,865,864,863,862,861,860,859,858,857,856,855,854,853,852,851,850,849,848,847,846,845,844,843,842,841,840,839,838,837,836,835,834,833,832,831,830,829,828,827,826,825,824,823,822,821,820,819,818,817,816,815,814,813,812,811,810,809,808,807,806,805,804,803
) and `status` != 3 order by c_time desc
    ) as t 
GROUP BY relate_group_id HAVING count(relate_group_id) >= 2
*/





INSERT INTO `weixin_group_log` (`id`) VALUES (DEFAULT)

去除值的引号
https://www.yidianphp.com/archives/905
$query1 = new \yii\db\Query();
$command1 = $query1
->from('user')
->where(['<=', 'age', new \yii\db\Expression('33 + 99')])
->createCommand();
echo $command1->rawSql; // SELECT * FROM `user` WHERE `age` <= 33 + 99
$query2 = new \yii\db\Query();
$expression = new \yii\db\Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)');
$command2 = $query2
->from('article')
->where(['date' => $expression])
->createCommand();
echo $command2->rawSql; //
$query1 = new \yii\db\Query();
$command1 = $query1
->from('user')
->where(['<=', 'age', new \yii\db\Expression('33 + 99')])
->createCommand();
echo $command1->rawSql; // SELECT * FROM `user` WHERE `age` <= 33 + 99
$query2 = new \yii\db\Query();
$expression = new \yii\db\Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)');
$command2 = $query2
->from('article')
->where(['date' => $expression])
->createCommand();
echo $command2->rawSql; //


User::find()->all();    此方法返回所有数据；

User::findOne($id);   此方法返回 主键 id=1  的一条数据(举个例子)； 

User::find()->where(['name' => '小伙儿'])->one();   此方法返回 ['name' => '小伙儿'] 的一条数据；

User::find()->where(['name' => '小伙儿'])->all();   此方法返回 ['name' => '小伙儿'] 的所有数据；

User::find()->orderBy('id DESC')->all();   此方法是排序查询；

User::findBySql('SELECT * FROM user')->all();  此方法是用 sql  语句查询 user 表里面的所有数据；

User::findBySql('SELECT * FROM user')->one();  此方法是用 sql  语句查询 user 表里面的一条数据；

User::find()->andWhere(['sex' => '男', 'age' => '24'])->count('id');   统计符合条件的总条数；

User::find()->andFilterWhere(['like', 'name', '小伙儿']); 此方法是用 like 查询 name 等于 小伙儿的 数据

User::find()->one();    此方法返回一条数据；

User::find()->all();    此方法返回所有数据；

User::find()->count();    此方法返回记录的数量；

User::find()->average();    此方法返回指定列的平均值；

User::find()->min();    此方法返回指定列的最小值 ；

User::find()->max();    此方法返回指定列的最大值 ；

User::find()->scalar();    此方法返回值的第一行第一列的查询结果；

User::find()->column();    此方法返回查询结果中的第一列的值；

User::find()->exists();    此方法返回一个值指示是否包含查询结果的数据行；

User::find()->batch(10);  每次取 10 条数据 

User::find()->each(10);  每次取 10 条数据， 迭代查询


    /**
     * 单个插入并获取id  $model->id
     * */
    public static function singleInserit($data, $safeOnly = false)
    {
        $model = new self();
        $model->setAttributes($data, $safeOnly); // true 验证rules false 不验证
        $model->insert();

        return $model;
    }
    
    /**
     * 数据批量插入
     * */
    public static function batchInserit($data)
    {
        if (empty($data)) {
            return 0;
        }
        $keys = array_keys($data[0]);
        return self::getDb()->createCommand()->batchInsert(self::tableName(), $keys, $data)->execute();
    }

    /**
     * 数据更新
     * */
    public static function batchUpdate($data, $where)
    {
        return self::updateAll($data, $where);
    }
    
    /**
     * 获取数据
     */
    public static function getList($where = [], $select = '', $indexBy = '', $order = '', $groupBy = '', $limit = '', $offset = '', $count = '', $isAll = true)
    {
        $query = self::find();
        if (!empty($select)) {
            $query->select($select);
        }

        if ($where) {
            $query->where($where);
        }
        if (!empty($indexBy)) {
            $query->indexBy($indexBy);
        }
        if (!empty($order)) {
            $query->orderBy($order);
        }
        if (!empty($groupBy)) {
            $query->groupBy($groupBy);
        }
        if (!empty($limit)) {
            $query->limit($limit);
        }
        if (!empty($offset)) {
            $query->offset($offset);
        }
        if (!empty($count)) {
            return $query->count();
        }

        if (empty($isAll)) {
            return $query->asArray()->one();
        } else {
            return $query->asArray()->all();
        }
    }
    